﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RegistroOTResumenRep')
	BEGIN
		DROP  Procedure  RegistroOTResumenRep
	END
GO 

CREATE PROCEDURE [dbo].[RegistroOTResumenRep]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT

	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 Total			DECIMAL(10,2))

	DECLARE @OTGastosIndirectos Table
		(NumeroOT		VARCHAR(30),
		 Monto			DECIMAL(10,2))

	DECLARE @OTRubro Table
		(NumeroOT		VARCHAR(30),
		 RubroNombre	VARCHAR(50),
		 Monto			DECIMAL(10,2))


	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, 'Catalog'			AS TableName	UNION
	SELECT 1 AS TableId, 'OrdenTrabajo'		AS TableName	UNION
	SELECT 2 AS TableId, 'OTRubro'			AS TableName
		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT o.NumeroOT, e.Nombre AS EmpresaNombre, FechaInicial, FechaFinal, 
			CASE o.Estado
                WHEN 'A' THEN 'EN PROCESO'
                WHEN 'T' THEN 'FINALIZADA'
			END AS Estado, o.Unidades As Cantidad, CONVERT(DECIMAL(10,2), 0) AS CantidadProducida
	FROM lit_ordenTrabajo o
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	WHERE o.Id = @id

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT
		UNION
		SELECT ot.NumeroOT, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT


	INSERT INTO @OTRubro
		SELECT NumeroOT, 'Mano de Obra Utilizada' As RubroNombre, SUM(Total) AS Monto
		FROM @OTDetalle
		GROUP BY NumeroOT
	
	-- OT Material
	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Materiales Utilizados' As RubroNombre, SUM(CostoArticuloTotal) AS Monto
		FROM Lit_OTMaterial m
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = m.OTId
		INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
		WHERE OTId = @id
		AND a.Emp_Id = @empresaId
		GROUP BY ot.NumeroOT

	-- OT Gastos Directos
	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Gastos Directos Aplicados' As RubroNombre, SUM(otg.Monto) As Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
		WHERE OTId = @id AND g.Tipo = 'D'
		GROUP BY ot.NumeroOT

	-- OT Gastos Indirectos
	INSERT INTO @OTGastosIndirectos
		SELECT ot.NumeroOT, otg.Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
		WHERE OTId = @id AND g.Tipo = 'I'
		UNION
		SELECT NumeroOT, ISNULL(ManoObraIndirecta,0) As Monto
		FROM Lit_OrdenTrabajo
		WHERE Id = @id
		AND ManoObraIndirecta IS NOT NULL
		AND ManoObraIndirecta > 0

	INSERT INTO @OTRubro
		SELECT NumeroOT, 'Gastos Indirectos Aplicados' As RubroNombre, SUM(Monto) As Monto
		FROM @OTGastosIndirectos
		GROUP BY NumeroOT

	INSERT INTO @OTRubro
		SELECT ot.NumeroOT, 'Máquinas Utilizadas' As RubroNombre, SUM(CostoMaquina) AS Monto
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		WHERE OTId = @id
		GROUP BY ot.NumeroOT

	SELECT *
	FROM @OTRubro
           	            
	SET NOCOUNT OFF
END
GO


